﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports Entidad
Public Class OrdenCompraDatos

    Private Path As String = "H:\DATOS\PRUEBA\"
    Private Path1 As String = "H:\DATOS\CFG\"
    Private nomSistema As String = "PRUEBA"

    Private Function generarCodigoDocumentoAlmacen(tdoc As String) As Integer
        Dim codigo As Integer
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT COUNT(*) + 1 AS Contar " &
                                  "FROM Documento_Almacen " &
                                  "WHERE TDOC_Codigo = '" & tdoc & "'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            Dim ds As New DataSet
            adapter.Fill(ds)

            codigo = CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return codigo
    End Function

    Function obtenerOrdenesCompraXFecha(fecha As String, flag1 As String, flag2 As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT docto, nprov, nompro, " &
                              "CONVERT(VARCHAR(15),fecha,103) AS FechaRegistro, " &
                              "CONVERT(VARCHAR(15),fecha1,103) AS FechaAlmacen, COUNT(*) AS NroItems, " &
                              "(CASE WHEN t2.DCP_Codigo IS NULL THEN '0' ELSE '1' END) AS Existe, " &
                              "CAST(ROUND(SUM(CASE WHEN ISNULL(ma.solicitado,0)>0 then CASE WHEN ma.solicitado-ma.atendido>0 then 0 else 1 end else 0 end)*100/COUNT(*),2) AS VARCHAR(10)) + ' %' AS Avance, " &
                              "(CASE WHEN CONVERT(VARCHAR(15),fecha1,103) = CONVERT(VARCHAR(15),GETDATE(),103) THEN '1' ELSE '0' END) AS Posterioridad " &
                              "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, p.nprov, a.nompro, P.fecha, p.fecha1, d.item FROM POH01 p " &
                              "INNER JOIN tablas/APPROV a ON a.prov = p.nprov AND a.procede " & IIf(flag1 = "1" And flag2 = "1", "LIKE ''%%''", IIf(flag1 = "1", "LIKE ''%2%''", "NOT LIKE ''%2%''")) & " INNER JOIN POD01 d " &
                              "ON d.docto = p.docto AND d.qenv > 0 WHERE p.fecha1 >= {" & fecha & "} AND VAL(p.status) < 5 AND p.ingalm = '''' AND p.nropar = 0') AS t1 " &
                              "LEFT JOIN Documento_Proveedor t2 " &
                              "ON t2.CIA = '01' AND t2.SUC_Codigo = '01' AND t2.TDOC_Codigo = 'OC' AND t2.DCP_Codigo = t1.docto " &
                              "LEFT JOIN VW_MostrarAvanceOrdenCompra ma " &
                              "ON ma.CIA = '01' AND ma.SUC_Codigo = '01' AND ma.TDOC_Codigo = 'OC' AND ma.DCP_Codigo = t1.docto " &
                              "AND ma.ART_Codigo = t1.item " &
                              "GROUP BY docto, nprov, nompro, fecha, fecha1, t2.DCP_Codigo " &
                              "ORDER BY fecha1"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerOrdenesCompraDetalleXOrdenAntes(codOrden As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT item, descrip, um, qenv " &
                              "FROM OPENQUERY(" & nomSistema & ", 'SELECT item, descrip, um, qenv " &
                              "FROM POD01 WHERE docto = ''" & codOrden & "'' AND qenv > 0')"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerOrdenesCompraDetalleXOrdenDespues(codOrden As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT ART_Codigo, ART_Descripcion, UNM_Abreviatura, Solicitado, Atendido, Avance, DAD_Codigo, DADP_Codigo, DADPU_Codigo " &
                              "FROM VW_MostrarAvanceOrdenCompra " &
                              "WHERE CIA = '01' AND SUC_Codigo = '01' AND TDOC_Codigo = 'OC' AND DCP_Codigo = '" & codOrden & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function registrarOrdenesCompra(codOrden As String) As Boolean
        Dim valor As Boolean = False

        Dim codigoDCA As Integer = generarCodigoDocumentoAlmacen("PI")

        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()

        Try
            coneccion.Open()
            '"SET IDENTITY_INSERT dbo.Documento_Proveedor_Detalle ON " &
            Dim query As String = "INSERT INTO Documento_Proveedor(CIA,SUC_Codigo,TDOC_Codigo,DCP_Codigo,DCP_Fecha,DCP_FechaReg,PROV_Codigo,EST_Codigo) " &
                                  "SELECT '01','01','OC',docto,GETDATE(),fecha,nprov,'02' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, p.nprov, p.fecha " &
                                  "FROM POH01 p WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = '''' AND p.nropar = 0') " &
                                  "WHERE docto NOT IN (SELECT DCP_Codigo FROM Documento_Proveedor)" &
                                  "INSERT INTO Documento_Proveedor_Detalle(CIA,SUC_Codigo,TDOC_Codigo,DCP_Codigo,DPD_Codigo,ART_Codigo,DPD_Costo,DPD_Cantidad,DPD_Activo) " &
                                  "SELECT '01','01','OC',docto,ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto),item,costo,qenv,'1' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, d.item, d.qenv, d.costo " &
                                  "FROM POH01 p INNER JOIN POD01 d ON d.docto = p.docto AND d.qenv > 0 " &
                                  "WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = '''' AND p.nropar = 0') " &
                                  "WHERE docto IN (SELECT DCP_Codigo FROM Documento_Proveedor) " &
                                  "INSERT INTO ArticuloUbicacion(CIA,SUC_Codigo,ALM_Codigo,ART_Codigo,UBI_Codigo,UAR_Stock,ASIG_UBI,UAR_Activo) " &
                                  "SELECT '01', '01', '00', item, '0000', 0, 0, '1' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, d.item, d.qenv " &
                                  "FROM POH01 p INNER JOIN POD01 d ON d.docto = p.docto AND d.qenv > 0 " &
                                  "WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = '''' AND p.nropar = 0') " &
                                  "WHERE item NOT IN (SELECT ART_Codigo FROM ArticuloUbicacion WHERE UBI_Codigo = '0000') " &
                                  "INSERT INTO Documento_Almacen(CIA,SUC_Codigo,TDOC_Codigo,DCA_Codigo,DCA_FechaDocumento,EST_Codigo) " &
                                  "SELECT '01','01','PI'," & codigoDCA & ",GETDATE(),'02' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, p.nprov, p.fecha " &
                                  "FROM POH01 p WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = ''''  AND p.nropar = 0') " &
                                  "WHERE " & codigoDCA & " NOT IN (SELECT DCA_Codigo FROM Documento_Almacen WHERE CIA = '01' AND SUC_Codigo = '01' AND TDOC_Codigo = 'PI') " &
                                  "INSERT INTO Documento_Almacen_Detalle(CIA,SUC,TDOC_Codigo,DCA_Codigo,DAD_Codigo,ART_Codigo,DAD_Cantidad,DAD_Activo) " &
                                  "SELECT '01','01','PI'," & codigoDCA & ",ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto),item,qenv,'1' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, d.item, d.qenv " &
                                  "FROM POH01 p INNER JOIN POD01 d ON d.docto = p.docto AND d.qenv > 0 " &
                                  "WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = '''' AND p.nropar = 0') " &
                                  "WHERE " & codigoDCA & " IN (SELECT DCA_Codigo FROM Documento_Almacen WHERE CIA = '01' AND SUC_Codigo = '01' AND TDOC_Codigo = 'PI') " &
                                  "INSERT INTO Documento_Almacen_Pickeador(CIA,SUC,TDOC_Codigo,DCA_Codigo,DAP_Codigo,ALM_Codigo,PICK_Codigo, " &
                                  "DAP_FechaAsig,DAP_FechaFinAsig,DAP_Orden,DAP_Activo) " &
                                  "SELECT '01','01','PI'," & codigoDCA & ",ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto),NULL, " &
                                  "'SIS001',GETDATE(),NULL,NULL,'1' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, p.nprov, p.fecha " &
                                  "FROM POH01 p WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = '''' AND p.nropar = 0') " &
                                  "WHERE " & codigoDCA & " IN (SELECT DCA_Codigo FROM Documento_Almacen WHERE CIA = '01' AND SUC_Codigo = '01' AND TDOC_Codigo = 'PI') " &
                                  "INSERT INTO Documento_Almacen_Detalle_Pickeador(DADP_Codigo,CIA,SUC_Codigo,TDOC_Codigo,DCA_Codigo,DAD_Codigo, " &
                                  "DAP_Codigo,PICK_Codigo,ALM_Codigo,DADP_Cantidad,DADP_FechaAsignacion,DADP_FechaInicio,DADP_FechaFin,DADP_CantPick, " &
                                  "DADP_Asig_Act,DADP_Activo) " &
                                  "SELECT ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto),'01','01','PI'," & codigoDCA & ", " &
                                  "ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto),ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto), " &
                                  "'SIS001','00', qenv, GETDATE(), NULL, NULL, 0, 0, '1' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, d.item, d.qenv " &
                                  "FROM POH01 p INNER JOIN POD01 d ON d.docto = p.docto AND d.qenv > 0 " &
                                  "WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = '''' AND p.nropar = 0') " &
                                  "WHERE " & codigoDCA & " IN (SELECT DCA_Codigo FROM Documento_Almacen WHERE CIA = '01' AND SUC_Codigo = '01' AND TDOC_Codigo = 'PI') " &
                                  "INSERT INTO Documento_Almacen_Detalle_Pickeador_Ubicacion(CIA,SUC_Codigo,TDOC_Codigo,DCA_Codigo,DADPU_Codigo, " &
                                  "ALM_Codigo,DADP_Codigo,ART_Codigo,UBI_Codigo,DADPU_Cantidad,DADPU_CantIng,DADPU_FechaAsig, " &
                                  "DADPU_FechaInicio,DADPU_FechaFin,DADPU_ErrorPicking,DADPU_Activo) " &
                                  "SELECT '01', '01', 'PI', " & codigoDCA & ", ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto), " &
                                  "'00', ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto), item, '0000', qenv, 0, GETDATE(), " &
                                  "NULL, NULL, 0, '1' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, d.item, d.qenv " &
                                  "FROM POH01 p INNER JOIN POD01 d ON d.docto = p.docto AND d.qenv > 0 " &
                                  "WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = '''' AND p.nropar = 0') " &
                                  "WHERE " & codigoDCA & " IN (SELECT DCA_Codigo FROM Documento_Almacen WHERE CIA = '01' AND SUC_Codigo = '01' AND TDOC_Codigo = 'PI') " &
                                  "INSERT INTO Documento_Proveedor_Almacen_Detalle(CIA_Proveedor,SUC_Proveedor,TDOC_Proveedor, " &
                                  "DCP_Codigo,DPD_Codigo,CIA_Almacen,SUC_Almacen,TDOC_Almacen,DCA_Codigo,DAD_Codigo,EST_Codigo,DCAD_Activo) " &
                                  "SELECT '01','01','OC',docto,ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto), " &
                                  "'01','01','PI'," & codigoDCA & ",ROW_NUMBER() OVER(PARTITION BY docto ORDER BY docto),'02','1' " &
                                  "FROM OPENQUERY(" & nomSistema & ", 'SELECT p.docto, d.item, d.qenv " &
                                  "FROM POH01 p INNER JOIN POD01 d ON d.docto = p.docto AND d.qenv > 0 " &
                                  "WHERE p.docto = ''" & codOrden & "'' AND VAL(p.status) < 5 AND ingalm = '''' AND p.nropar = 0') " &
                                  "WHERE docto IN (SELECT DCP_Codigo FROM Documento_Proveedor) AND " & codigoDCA & " IN (SELECT DCA_Codigo FROM Documento_Almacen WHERE CIA = '01' AND SUC_Codigo = '01' AND TDOC_Codigo = 'PI')"

            Dim comando As New SqlCommand
            With comando
                .Connection = coneccion
                .CommandText = query
                .ExecuteNonQuery()
            End With

            valor = True
        Catch ex As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    Function obtenerOrdenesCompraDetalle(codCIA As String, codSUC As String, codTDO As String, codDCP As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT a.ART_Codigo, a.ART_Descripcion, um.UNM_Abreviatura, dpd.DPD_Cantidad, dpd.DPD_Costo, " &
                              "(CASE WHEN ISNULL(tabla1.Contar,0) > 0 THEN '1' ELSE '0' END) AS CondBarra, " &
                              "(CASE WHEN ISNULL(tabla2.Contar,0) > 0 THEN '1' ELSE '0' END) AS CondCB " &
                              "FROM Documento_Proveedor_Detalle dpd " &
                              "LEFT JOIN (SELECT ART_Codigo, COUNT(*) AS Contar " &
                              "FROM ArticuloProveedorCodBarra " &
                              "WHERE SUBSTRING(BAR_Codigo,1,2) <> 'CB' AND APC_Nivel = '1' " &
                              "GROUP BY ART_Codigo) tabla1 " &
                              "ON tabla1.ART_Codigo = dpd.ART_Codigo " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = dpd.ART_Codigo AND a.ART_Activo = '1' " &
                              "INNER JOIN UnidadMedida um " &
                              "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                              "LEFT JOIN (SELECT ART_Codigo, COUNT(*) AS Contar " &
                              "FROM ArticuloProveedorCodBarra " &
                              "WHERE SUBSTRING(BAR_Codigo,1,2) = 'CB' AND APC_Nivel = '1' " &
                              "GROUP BY ART_Codigo) tabla2 " &
                              "ON tabla2.ART_Codigo = dpd.ART_Codigo " &
                              "WHERE DCP_Codigo = @DCP AND TDOC_Codigo = @TDO AND SUC_Codigo = @SUC AND CIA = @CIA"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@CIA", codCIA)
            .Parameters.AddWithValue("@SUC", codSUC)
            .Parameters.AddWithValue("@TDO", codTDO)
            .Parameters.AddWithValue("@DCP", codDCP)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerOrdenCompra(codCIA As String, codSUC As String, codTDO As String, codDCP As String) As OrdenCompra
        Dim oc As OrdenCompra = Nothing
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()

        Try
            coneccion.Open()

            Dim query As String = "SELECT dp.CIA, dp.SUC_Codigo, dp.TDOC_Codigo, dp.DCP_Codigo, " &
                                  "CONVERT(VARCHAR(15),DCP_Fecha,103) + ' ' + CONVERT(VARCHAR(15),DCP_Fecha,108) AS FechaRegistro, " &
                                  "CONVERT(VARCHAR(15),DCP_FechaReg,103) + ' ' + CONVERT(VARCHAR(15),DCP_FechaReg,108) AS FechaLlegada, " &
                                  "dp.PROV_Codigo, p.PROV_DesCorta, EST_Codigo, COUNT(*) AS CantItems " &
                                  "FROM Documento_Proveedor dp " &
                                  "LEFT JOIN Proveedor p " &
                                  "ON p.PROV_Codigo = dp.PROV_Codigo " &
                                  "INNER JOIN Documento_Proveedor_Detalle dpd " &
                                  "ON dpd.CIA = dp.CIA AND dpd.TDOC_Codigo = dp.TDOC_Codigo AND dpd.DCP_Codigo = dp.DCP_Codigo AND dpd.DPD_Activo = '1' " &
                                  "WHERE dp.CIA = @CIA AND dp.SUC_Codigo = @SUC AND dp.TDOC_Codigo = @TDO AND dp.DCP_Codigo = @DCP " &
                                  "GROUP BY dp.CIA, dp.SUC_Codigo, dp.TDOC_Codigo, dp.DCP_Codigo, DCP_Fecha, DCP_FechaReg, dp.PROV_Codigo, p.PROV_DesCorta, EST_Codigo"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDO", codTDO)
                .Parameters.AddWithValue("@DCP", codDCP)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then
                oc = New OrdenCompra(ds.Tables(0).Rows(0)("CIA").ToString.Trim, ds.Tables(0).Rows(0)("SUC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("TDOC_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("DCP_Codigo").ToString.Trim,
                                     ds.Tables(0).Rows(0)("FechaLlegada").ToString.Trim, ds.Tables(0).Rows(0)("FechaRegistro").ToString.Trim, ds.Tables(0).Rows(0)("PROV_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("PROV_DesCorta").ToString.Trim, ds.Tables(0).Rows(0)("EST_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CantItems").ToString.Trim)
            Else
                oc = New OrdenCompra("-1", "", "", "", "", "", "", "", "", "")
            End If
        Catch ex As Exception
            oc = Nothing
        Finally
            coneccion.Close()
        End Try

        Return oc
    End Function

    'Actualiza el nroPar y Ingalm del SIS segun OC
    Function actualizarPIngresoFlagAlmacen(nroPar As String, flag As String, codOrden As String) As Boolean
        Dim valor As Boolean = False
        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "POH01.DBF") & ";"
        Dim query As String = "UPDATE POH01 SET nropar = " & nroPar & ", ingalm = '" & flag & "', fecalm = DATE() WHERE docto = '" & codOrden & "'"
        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
                valor = True
            Catch ex As Exception
                valor = False
            Finally
                dbConn.Close()
            End Try
        End Using
        Return valor
    End Function

    Function actualizarCantidadRealItem(codOrden As String, cantidad As Integer, codArticulo As String) As Boolean
        Dim valor As Boolean = False
        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "POD01.DBF") & ";"
        Dim query As String = "UPDATE POD01 SET n01 = " & cantidad & " WHERE docto = '" & codOrden & "' AND item = '" & codArticulo & "'"
        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
                valor = True
            Catch ex As Exception
                valor = False
            Finally
                dbConn.Close()
            End Try
        End Using
        Return valor
    End Function

    Function obtenerPeriodo(codCIA As String) As String
        Dim valor As String = ""
        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "MATROCFG.DBF") & ";"
        Dim query As String = "SELECT anhos + meses AS Periodo FROM MATROCFG WHERE codcia = '" & codCIA & "'"
        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                End With
                Dim ds As New DataSet
                Dim adapter As New OdbcDataAdapter(comando)
                adapter.Fill(ds)

                If ds.Tables(0).Rows.Count > 0 Then
                    valor = ds.Tables(0).Rows(0)("Periodo").ToString.Trim.Substring(2)
                End If
            Catch ex As Exception
                valor = ""
            Finally
                dbConn.Close()
            End Try
        End Using
        Return valor
    End Function

    Function actualizarCantidadManoOrden(cantidad As Integer, codArticulo As String) As Boolean
        Dim valor As Boolean = False
        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + obtenerPeriodo("01") + "\INVMAST.DBF") & ";"
        Dim query As String = "UPDATE INVMAST SET mano = mano + " & cantidad & " , orden = orden - " & cantidad & " WHERE item = '" & codArticulo & "'"
        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
                valor = True
            Catch ex As Exception
                valor = False
            Finally
                dbConn.Close()
            End Try
        End Using
        Return valor
    End Function

    Function obtenerNroParteIngreso(cia As String, suc As String, tdoc As String, codOrden As String) As String
        Dim codigo As String = ""
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT DCA_Codigo " &
                                  "FROM Documento_Proveedor_Almacen_Detalle " &
                                  "WHERE CIA_Proveedor = @CIA AND SUC_Proveedor = @SUC " &
                                  "AND TDOC_Proveedor = @TDO AND DCP_Codigo = @COD " &
                                  "GROUP BY CIA_Almacen, SUC_Almacen, TDOC_Almacen, DCA_Codigo"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@CIA", cia)
                .Parameters.AddWithValue("@SUC", suc)
                .Parameters.AddWithValue("@TDO", tdoc)
                .Parameters.AddWithValue("@COD", codOrden)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then
                codigo = ds.Tables(0).Rows(0)("DCA_Codigo").ToString.Trim
            End If
        Catch e As Exception
            codigo = "X"
        Finally
            coneccion.Close()
        End Try

        Return codigo
    End Function

    Function actualizarCantidadesParteIngreso(cia As String, suc As String, tdo As String, dcaCodigo As String, dadCodigo As String, dadpCodigo As String, dadpuCodigo As String, artCodigo As String, cantidad As Integer) As Boolean
        Dim valor As Boolean = False
        Dim query As String = ""
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar
        coneccion.Open()
        Dim comand As SqlCommand = coneccion.CreateCommand()
        Dim transaction As SqlTransaction

        transaction = coneccion.BeginTransaction("SampleTransaction")
        comand.Connection = coneccion
        comand.Transaction = transaction
        Try
            query = "UPDATE Documento_Almacen_Detalle " &
                    "SET DAD_Cantidad = @CAN " &
                    "WHERE CIA = @CIA AND SUC = @SUC " &
                    "AND TDOC_Codigo = @TDO AND DCA_Codigo = @DCA " &
                    "AND DAD_Codigo = @DAD AND ART_Codigo = @ART"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@CAN", cantidad)
                .Parameters.AddWithValue("@CIA", cia)
                .Parameters.AddWithValue("@SUC", suc)
                .Parameters.AddWithValue("@TDO", tdo)
                .Parameters.AddWithValue("@DCA", dcaCodigo)
                .Parameters.AddWithValue("@DAD", dadCodigo)
                .Parameters.AddWithValue("@ART", artCodigo)
                .ExecuteNonQuery()
            End With

            query = "UPDATE dadp " &
                    "SET dadp.DADP_Cantidad = @CAN " &
                    "FROM Documento_Almacen_Detalle_Pickeador dadp " &
                    "INNER JOIN Documento_Almacen_Detalle dad " &
                    "ON dad.CIA = dadp.CIA AND dad.SUC = dadp.SUC_Codigo " &
                    "AND dad.TDOC_Codigo = dadp.TDOC_Codigo AND dad.DCA_Codigo = dadp.DCA_Codigo " &
                    "AND dad.DAD_Codigo = dadp.DAD_Codigo AND dad.DAD_Activo = '1' AND dad.ART_Codigo = @ART " &
                    "WHERE dadp.CIA = @CIA AND dadp.SUC_Codigo = @SUC " &
                    "AND dadp.TDOC_Codigo = @TDO AND dadp.DCA_Codigo = @DCA AND dadp.DAD_Codigo = @DAD AND dadp.DADP_Codigo = @DADP"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@DADP", dadpCodigo)
                .ExecuteNonQuery()
            End With

            query = "UPDATE Documento_Almacen_Detalle_Pickeador_Ubicacion " &
                    "SET DADPU_Cantidad = @CAN " &
                    "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDO " &
                    "AND DCA_Codigo = @DCA AND DADPU_Codigo = @DPU"

            With comand
                .CommandText = query
                .Parameters.AddWithValue("@DPU", dadpuCodigo)
                .ExecuteNonQuery()
            End With

            query = "UPDATE Kardex_Ubicacion " &
                    "SET KUC_Cantidad = @CAN " &
                    "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDO AND KUC_Documento = @DCA " &
                    "AND UBI_Codigo = '0000' AND KUC_Detalle = @DAD AND ART_Codigo = @ART"

            With comand
                .CommandText = query
                .ExecuteNonQuery()
            End With

            transaction.Commit()
            valor = True
        Catch e As Exception
            transaction.Rollback()
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    Function obtenerOrdenCompraDetalle(codCIA As String, codSUC As String, codTDO As String, codDCP As String, codArticulo As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim con As SqlConnection = conex.conectar()

        Try
            con.Open()

            Dim query As String = "SELECT * " &
                                  "FROM Documento_Proveedor_Detalle " &
                                  "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDO " &
                                  "AND DCP_Codigo = @DCP AND ART_Codigo = @ART"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDO", codTDO)
                .Parameters.AddWithValue("@DCP", codDCP)
                .Parameters.AddWithValue("@ART", codArticulo)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function registrarOrdenCompraDetalle(codCIA As String, codSUC As String, codTDO As String, codDCP As String, codDPD As String, codArticulo As String, costo As String, cantidad As String, activo As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim con As SqlConnection = conex.conectar()

        Try
            con.Open()

            Dim query As String = "INSERT INTO Documento_Proveedor_Detalle(CIA, SUC_Codigo, TDOC_Codigo, DCP_Codigo, " &
                                  "DPD_Codigo, ART_Codigo, DPD_Costo, DPD_Cantidad, DPD_Activo) " &
                                  "VALUES(@CIA, @SUC, @TDO, @DCP, @DPD, @ART, @COS, @CAN, @ACT)"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDO", codTDO)
                .Parameters.AddWithValue("@DCP", codDCP)
                .Parameters.AddWithValue("@DPD", codDPD)
                .Parameters.AddWithValue("@ART", codArticulo)
                .Parameters.AddWithValue("@COS", costo)
                .Parameters.AddWithValue("@CAN", cantidad)
                .Parameters.AddWithValue("@ACT", activo)
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function obtenerCodigoAutogeneradoDetalle(codTDO As String, codDCP As String) As String
        Dim codigo As String = ""
        Dim conex As New Conexion
        Dim con As SqlConnection = conex.conectar()

        Try
            con.Open()

            Dim query As String = "SELECT * " &
                                  "FROM Documento_Proveedor_Detalle " &
                                  "WHERE TDOC_Codigo = @TDO AND DCP_Codigo = @DCP"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@TDO", codTDO)
                .Parameters.AddWithValue("@DCP", codDCP)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            Dim ds As New DataSet
            adapter.Fill(ds)

            codigo = ds.Tables(0).Rows.Count + 1
        Catch ex As Exception
            codigo = "-1"
        Finally
            con.Close()
        End Try

        Return codigo
    End Function

    Function actualizarOrdenCompraDetalle(codCIA As String, codSUC As String, codTDO As String, codDCP As String, codArticulo1 As String, codArticulo2 As String, cantidad As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim con As SqlConnection = conex.conectar()

        Try
            con.Open()

            Dim query As String = "UPDATE Documento_Proveedor_Detalle " &
                                  "SET DPD_Cantidad = @CAN, ART_Codigo = @ART1 " &
                                  "WHERE CIA = @CIA AND SUC_Codigo = @SUC AND TDOC_Codigo = @TDO " &
                                  "AND DCP_Codigo = @DCP AND ART_Codigo = @ART2"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDO", codTDO)
                .Parameters.AddWithValue("@DCP", codDCP)
                .Parameters.AddWithValue("@ART1", codArticulo1)
                .Parameters.AddWithValue("@CAN", cantidad)
                .Parameters.AddWithValue("@ART2", codArticulo2)
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

End Class
